﻿<%@ WebHandler Language="C#" Class="ContainerProcessInfoReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 批次生产进度查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.5
 **/

public class ContainerProcessInfoReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    uMESCommonMethod cm = new uMESCommonMethod();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT cci.containerid,cci.familylistnames,cci.productname,cci.productdescription,
       cci.containername,cci.qty,cci.workflowstepname,cci.workcentername,c.originalstartdate,
       CASE WHEN cci.isship = 1 THEN '转工中'
            WHEN csr.dispatchemployeeid IS NULL THEN '待派工'
            WHEN csr.dispatchemployeeid IS NOT NULL AND csr.reportemployeeid IS NULL THEN '待报工'
            WHEN csr.reportemployeeid IS NOT NULL AND csr.checkemployeeid IS NULL THEN '待检验'
       END status,
       CASE WHEN fp.productname IS NULL THEN '非重点件号'
            WHEN fp.productname IS NOT NULL AND fp.bigproductname IS NULL THEN '工区重点件号'
            WHEN fp.productname IS NOT NULL AND fp.bigproductname IS NOT NULL THEN '分厂重点件号'
       END producttype
FROM containercurrentinfo cci
LEFT JOIN containersteprecord csr on csr.containerid = cci.containerid AND csr.workflowstepid = cci.workflowstepid
LEFT JOIN container c ON c.containerid = cci.containerid 
LEFT JOIN focusproduct fp ON fp.productname = cci.productname AND fp.factoryid IS NOT NULL 
WHERE cci.isinstore = 0 AND cci.containerstatus = 1
";
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 产品类型
        if (jObject["producttype"] != null && jObject["producttype"].ToString() != "")
        {
            if (jObject["producttype"].ToString() == "非重点件号")
            {
                sql += " AND fp.productname IS NULL ";
            }
            if (jObject["producttype"].ToString() == "分厂重点件号")
            {
                sql += " AND fp.bigproductname IS NOT NULL AND fp.productname IS NOT NULL ";
            }
            if (jObject["producttype"].ToString() == "工区重点件号")
            {
                sql += " AND fp.bigproductname IS NULL AND fp.productname IS NOT NULL ";
            }
        }

        // 开卡时间
        if (jObject["containerstartdate"] != null && jObject["containerstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["containerstartdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND c.originalstartdate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND c.originalstartdate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        dt.Columns.Add("specCheckedRate");//工序完成百分比
        dt.Columns.Add("workTimeRate");//工时完成百分比

        StringBuilder sbContainerId = new StringBuilder();
        if (dt.Rows.Count > 0)
        {
            sbContainerId.Append("(");
            foreach (DataRow dr in dt.Rows)
            {
                sbContainerId.AppendFormat("cci.containerid = '{0}' OR ", dr["containerid"].ToString());
            }
            sbContainerId.Remove(sbContainerId.Length - 3, 3);
            sbContainerId.Append(")");
        }
        else
        {
            sbContainerId.Append("(1=0)");
        }


        DataTable dtSpecRate = getSpecCompletePercent(sbContainerId.ToString());
        DataTable dtWoktTimeRate = getWorkTimeCompletePercent(sbContainerId.ToString());

        foreach (DataRow dr in dt.Rows)
        {
            string filter = string.Format("containerid = '{0}'", dr["containerid"]);
            DataRow[] drs = dtSpecRate.Select(filter);
            if (drs.Length > 0)
            {
                dr["specCheckedRate"] = drs[0]["specCompleteRate"];
            }
            if (dr["workflowstepname"].ToString() == "完工入库")
            {
                dr["specCheckedRate"] = "100%";
                dr["workTimeRate"] = "100%";
            }

            //计算工时完成百分比
            filter = string.Format("containerid = '{0}'", dr["containerid"]);
            drs = dtWoktTimeRate.Select(filter);
            if (drs.Length > 0)
            {
                dr["workTimeRate"] = drs[0]["workTimeRate"];
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        DataTable dt = dal.GetDataTable(getQuerySql(jObject));
        dt.Columns.Add("specCheckedRate");//工序完成百分比
        dt.Columns.Add("workTimeRate");//工时完成百分比

        StringBuilder sbContainerId = new StringBuilder();
        sbContainerId.Append("(");
        if (dt.Rows.Count > 0)
        {
            foreach (DataRow dr in dt.Rows)
            {
                sbContainerId.AppendFormat("cci.containerid = '{0}' OR ", dr["containerid"].ToString());
            }
            sbContainerId.Remove(sbContainerId.Length - 3, 3);
            sbContainerId.Append(")");
        }
        else
        {
            sbContainerId.Append("1=1)");
        }


        DataTable dtSpecRate = getSpecCompletePercent(sbContainerId.ToString());
        DataTable dtWoktTimeRate = getWorkTimeCompletePercent(sbContainerId.ToString());

        foreach (DataRow dr in dt.Rows)
        {
            string filter = string.Format("containerid = '{0}'", dr["containerid"]);
            DataRow[] drs = dtSpecRate.Select(filter);
            if (drs.Length > 0)
            {
                dr["specCheckedRate"] = drs[0]["specCompleteRate"];
            }
            if (dr["workflowstepname"].ToString() == "完工入库")
            {
                dr["specCheckedRate"] = "100%";
                dr["workTimeRate"] = "100%";
            }

            //计算工时完成百分比
            filter = string.Format("containerid = '{0}'", dr["containerid"]);
            drs = dtWoktTimeRate.Select(filter);
            if (drs.Length > 0)
            {
                dr["workTimeRate"] = drs[0]["workTimeRate"];
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dt.Columns.Remove("containerid");
        dt.Columns["familylistnames"].Caption = "机型";
        dt.Columns["productname"].Caption = "零件图号";
        dt.Columns["productdescription"].Caption = "零件名称";
        dt.Columns["containername"].Caption = "流水卡号";
        dt.Columns["qty"].Caption = "在制数";
        dt.Columns["workflowstepname"].Caption = "当前工序";
        dt.Columns["originalstartdate"].Caption = "开卡时间";
        dt.Columns["workcentername"].Caption = "责任工区";
        dt.Columns["producttype"].Caption = "件号类型";
        dt.Columns["status"].Caption = "状态";
        dt.Columns["speccheckedrate"].Caption = "工序完成百分比";
        dt.Columns["worktimerate"].Caption = "工时完成百分比";
        ExportHelper.ExportExcel(dt, "批次生产进度查询报表");
    }

    DataTable getSpecCompletePercent(string containerList)
    {
        //查询流水卡工序总数
        StringBuilder sb = new StringBuilder();
        sb = new StringBuilder();
        sb.AppendLine("SELECT cci.containerid,COUNT(ws.workflowid) specTotal");
        sb.AppendLine("FROM containercurrentinfo cci");
        sb.AppendLine("LEFT JOIN workflow wf ON cci.workflowid = wf.workflowid");
        sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowid = wf.workflowid");
        sb.AppendLine("WHERE cci.isinstore = 0 AND " + containerList + "");
        sb.AppendLine("GROUP BY cci.containerid");
        DataTable dtResult = dal.GetDataTable(sb.ToString());
        dtResult.Columns.Add("specCompleteRate", typeof(string));// 工序完成百分比

        sb = new StringBuilder();
        sb.AppendLine("SELECT t.containerid,SUM(t.specQty) specQty ");
        sb.AppendLine("FROM ( ");
        //查询外委工序数(批次未入库)
        sb.AppendLine("       SELECT cm.containerid,(ws2.sequence - ws.sequence) specQty");
        sb.AppendLine("       FROM containercommissioning cm");
        sb.AppendLine("       LEFT JOIN containercurrentinfo cci ON cci.containerid = cm.containerid");
        sb.AppendLine("       LEFT JOIN workflowstep ws ON ws.workflowstepid = cm.workflowstepid");
        sb.AppendLine("       LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cm.inworkflowstepid");
        sb.AppendLine("       WHERE cci.isinstore = 0 AND cm.indate IS NOT NULL AND " + containerList + "");
        //查询转工工序数       
        sb.AppendLine("       UNION ALL ");
        sb.AppendLine("       SELECT M.containerid,SUM(M.specQty) specQty");
        sb.AppendLine("       FROM (");
        sb.AppendLine("             SELECT cci.containerid,cr.containershiprecordid,(MAX(ws.sequence) - MIN(ws.sequence))+1 specQty");
        sb.AppendLine("             FROM containershiprecive cr");
        sb.AppendLine("             LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid");
        sb.AppendLine("             LEFT JOIN containershiprecordstep csd ON csd.containershiprecordid = cr.containershiprecordid");
        sb.AppendLine("             LEFT JOIN workflowstep ws ON csd.workflowstepid = ws.workflowstepid");
        sb.AppendLine("             WHERE cci.isinstore = 0 AND cr.reciver IS NOT NULL AND " + containerList + "");
        sb.AppendLine("             GROUP BY cci.containerid,cr.containershiprecordid");
        sb.AppendLine("       ) M ");
        sb.AppendLine("       GROUP BY M.containerid");
        //查询检验工序数（在containersteprecord内排除掉那些在外协过程中产生的报工检验记录）
        sb.AppendLine("       UNION ALL ");
        sb.AppendLine("       SELECT csr.containerid,COUNT(DISTINCT csr.workflowstepname) specQty");
        sb.AppendLine("       FROM containercurrentinfo cci");
        sb.AppendLine("       LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid");
        sb.AppendLine("       LEFT JOIN sessionvalues sv ON sv.employeeid = csr.checkemployeeid");
        sb.AppendLine("       WHERE cci.isinstore = 0 AND csr.usecompleted = 1 AND csr.checkemployeeid IS NOT NULL");
        sb.AppendLine("             AND csr.factoryid = cci.originalfactoryid AND " + containerList + "");
        sb.AppendLine("       GROUP BY csr.containerid");
        sb.AppendLine(") t ");
        sb.AppendLine("GROUP BY t.containerid");
        DataTable dtSpecCheckedCount = dal.GetDataTable(sb.ToString());

        foreach (DataRow dr in dtResult.Rows)
        {
            //完工工序数
            DataRow[] drs = dtSpecCheckedCount.Select(string.Format(" containerid = '{0}'", dr["containerid"]));
            if (drs.Length > 0)
            {
                decimal specTotal = Convert.ToDecimal(dr["specTotal"].ToString());
                decimal specCheckedCount = Convert.ToDecimal(drs[0]["specQty"].ToString());
                if (specTotal != 0)
                {
                    if (specCheckedCount == specTotal)
                    {

                    }
                    else
                    {
                        dr["specCompleteRate"] = ((specCheckedCount / specTotal) * 100).ToString("0.00");
                    }
                }
                else
                {
                    dr["specCompleteRate"] = "0";
                }
            }
            else
            {
                dr["specCompleteRate"] = "0";
            }
        }
        return dtResult;
    }

    DataTable getWorkTimeCompletePercent(string containerList)
    {
        StringBuilder sb = new StringBuilder();
        // 查询流水卡总工时
        sb = new StringBuilder();
        sb.AppendLine("SELECT cci.containerid,SUM(nvl(st.durationperunit,0)) workTimeAll ");
        sb.AppendLine("FROM containercurrentinfo cci");
        sb.AppendLine("LEFT JOIN workflow wf ON cci.workflowid = wf.workflowid");
        sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowid = wf.workflowid");
        sb.AppendLine("LEFT JOIN stepschedulingdetail st on st.stepid = ws.workflowstepid");
        sb.AppendLine("WHERE cci.isinstore = 0 AND " + containerList + "");
        sb.AppendLine("GROUP BY cci.containerid");

        DataTable dtResult = dal.GetDataTable(sb.ToString());
        dtResult.Columns.Add("workTimeRate", typeof(string)); // 工时完成百分比

        sb = new StringBuilder();
        // 完工工时 = 外委工时+转工工时+检验工时
        sb.AppendLine("SELECT T.containerid,SUM(T.workTime) workTime ");
        sb.AppendLine("FROM ( ");
        //查询外委工时(批次未入库)
        sb.AppendLine("       SELECT cm.containerid,SUM(nvl(st.durationperunit,0)) workTime");
        sb.AppendLine("       FROM containercommissioning cm");
        sb.AppendLine("       LEFT JOIN containercurrentinfo cci ON cci.containerid = cm.containerid");
        sb.AppendLine("       LEFT JOIN stepschedulingdetail st on (st.stepid = cm.workflowstepid OR st.stepid = cm.inworkflowstepid)");
        sb.AppendLine("       WHERE cci.isinstore = 0 AND cm.indate IS NOT NULL AND " + containerList + "");
        sb.AppendLine("       GROUP BY cm.containerid");
        //查询转工工时      
        sb.AppendLine("       UNION ALL ");
        sb.AppendLine("       SELECT M.containerid,SUM(M.workTime) workTime");
        sb.AppendLine("       FROM (");
        sb.AppendLine("             SELECT cci.containerid,cr.containershiprecordid,SUM(nvl(st.durationperunit,0)) workTime");
        sb.AppendLine("             FROM containershiprecive cr");
        sb.AppendLine("             LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid");
        sb.AppendLine("             LEFT JOIN containershiprecordstep csd ON csd.containershiprecordid = cr.containershiprecordid");
        sb.AppendLine("             LEFT JOIN workflowstep ws ON csd.workflowstepid = ws.workflowstepid");
        sb.AppendLine("             LEFT JOIN stepschedulingdetail st on st.stepid = ws.workflowstepid");
        sb.AppendLine("             WHERE cci.isinstore = 0 AND cr.reciver IS NOT NULL AND " + containerList + "");
        sb.AppendLine("             GROUP BY cci.containerid,cr.containershiprecordid");
        sb.AppendLine("       ) M ");
        sb.AppendLine("       GROUP BY M.containerid");
        //查询检验工时（在containersteprecord内排除掉那些在外协过程中产生的报工检验记录）
        sb.AppendLine("       UNION ALL ");
        sb.AppendLine("       SELECT M.containerid,SUM(nvl(st.durationperunit,0)) workTime ");
        sb.AppendLine("       FROM (");
        sb.AppendLine("             SELECT DISTINCT csr.containerid,csr.workflowstepid");
        sb.AppendLine("             FROM containercurrentinfo cci");
        sb.AppendLine("             LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid");
        sb.AppendLine("             LEFT JOIN sessionvalues sv ON sv.employeeid = csr.checkemployeeid");
        sb.AppendLine("             WHERE cci.isinstore = 0 AND csr.usecompleted = 1 AND csr.checkemployeeid IS NOT NULL");
        sb.AppendLine("             AND csr.factoryid = cci.originalfactoryid AND " + containerList + "");
        sb.AppendLine("       ) M");
        sb.AppendLine("       LEFT JOIN stepschedulingdetail st on st.stepid = M.workflowstepid");
        sb.AppendLine("       GROUP BY M.containerid");
        sb.AppendLine(") T ");
        sb.AppendLine("GROUP BY T.containerid");

        DataTable dtCurrentWorkTime = dal.GetDataTable(sb.ToString());

        foreach (DataRow dr in dtResult.Rows)
        {
            DataRow[] drs = dtCurrentWorkTime.Select(string.Format(" containerid = '{0}'", dr["containerid"]));
            if (drs.Length > 0)
            {
                decimal workTimeAll = Convert.ToDecimal(dr["workTimeAll"].ToString());
                decimal workTimeComplete = Convert.ToDecimal(drs[0]["workTime"].ToString());
                if (workTimeAll != 0)
                {
                    if (workTimeComplete == workTimeAll)
                    {
                        dr["workTimeRate"] = "100";
                    }
                    else
                    {
                        dr["workTimeRate"] = ((workTimeComplete / workTimeAll) * 100).ToString("0.00");
                    }
                }
                else
                {
                    dr["workTimeRate"] = "0";
                }
            }
            else
            {
                dr["workTimeRate"] = "0";
            }
        }
        return dtResult;
    }
}